package com.hn.comm.excel;

import handler.BaseHandler;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelHandler {

	/**
	 * list转 map
	 * 
	 * @param map
	 * @param key
	 * @return
	 */
	public static List getListFromMap(Map map, List<String> key) {
		List listBack = new ArrayList();

		for (String k : key) {
			Object v = map.get(k);
			listBack.add(v);
		}
		return listBack;
	}

	public static ExcelModel getExcelData(String file) {

		ExcelModel excelModel = new ExcelModel();
		List<Map<String, String>> listQA = new ArrayList();

		InputStream is = null;
		Workbook wb = null;
		try {
			is = new FileInputStream(file);
			wb = Workbook.getWorkbook(is);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Sheet sheet = wb.getSheet(0);
		String sheetName = sheet.getName();
		// System.out.println("sheet name=" + sheetName);

		if (sheet != null) {
			// 列数
			int rsColumns = sheet.getColumns();
			// System.out.println("列数=" + rsColumns);

			// 总行数
			int rsRows = sheet.getRows();
			// System.out.println("行数=" + rsRows);

			// 得到书本属性
			Cell[] cellTitle = sheet.getRow(0);
			excelModel.setBookID(cellTitle[0].getContents());
			excelModel.setBookNameChinese(cellTitle[1].getContents());
			excelModel.setBookNameEnglish(cellTitle[2].getContents());
			excelModel.setBooknameAbridge(cellTitle[3].getContents());

			// 得到列的KEY
			Cell[] cellHeader = sheet.getRow(1);
			List<String> listHeader = new ArrayList<String>();
			for (int ic = 0; ic < cellHeader.length; ic++) {
				listHeader.add(cellHeader[ic].getContents());
			}
			excelModel.setHeader(listHeader);

			for (int rowNum = 2; rowNum < rsRows; rowNum++) {
				Map<String, String> cellMap = new HashMap<String, String>();
				Cell[] cells = sheet.getRow(rowNum);
				for (int j = 0; j < cells.length; j++) {
					String c = cells[j].getContents();
					cellMap.put(listHeader.get(j), cells[j].getContents());
				}

				if (null != cellMap.get(BaseHandler.HEAD_CHINESE)
						&& !cellMap.get(BaseHandler.HEAD_CHINESE).equals("")) {
					listQA.add(cellMap);
				}
			}

		}
		try {
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		excelModel.setContent(listQA);
		return excelModel;
	}

	/**
	 * 写入excel
	 * 
	 * @param eModel
	 * @param file
	 */
	public static void writeExcel(ExcelModel eModel, String file) {
		try {
			OutputStream out = new FileOutputStream(new File(file));

			executeWriteExcel(out, eModel);
			out.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * @param datas
	 *            封装着Object[]的列表, 一般是String内容.
	 * @param title
	 *            每个sheet里的标题.
	 */
	public static void executeWriteExcel(OutputStream out, ExcelModel eModel) {
		if (eModel == null) {
			throw new IllegalArgumentException("写excel流需要List参数!");
		}
		try {
			WritableWorkbook workbook = Workbook.createWorkbook(out);
			WritableSheet ws = workbook.createSheet("sheet 1", 0);
			int rowNum = 0; // 要写的行
			// 写入书本属性
			List<String> bookStuff = new ArrayList<String>();
			bookStuff.add(eModel.getBookID());
			bookStuff.add(eModel.getBookNameChinese());
			bookStuff.add(eModel.getBookNameEnglish());
			putRow(ws, rowNum, bookStuff);// 压入书属性
			rowNum++;

			if (eModel.getHeader() != null) {
				putRow(ws, rowNum, (eModel.getHeader()));// 压入标题
				rowNum++;
			}
			for (int i = 0; i < eModel.getContent().size(); i++, rowNum++) {// 写sheet
				List listCell = getListFromMap(eModel.getContent().get(i),
						eModel.getHeader());
				putRow(ws, rowNum, listCell); // 压一行到sheet
			}
			workbook.write();
			workbook.close(); // 一定要关闭, 否则没有保存Excel
		} catch (RowsExceededException e) {
			System.out.println("jxl write RowsExceededException: "
					+ e.getMessage());
		} catch (WriteException e) {
			System.out.println("jxl write WriteException: " + e.getMessage());
		} catch (IOException e) {
			System.out.println("jxl write file i/o exception!, cause by: "
					+ e.getMessage());
		}
	}

	private static void putRow(WritableSheet ws, int rowNum, List cells)
			throws RowsExceededException, WriteException {
		for (int j = 0; j < cells.size(); j++) {// 写一行
			Label cell = new Label(j, rowNum, "" + cells.get(j));
			ws.addCell(cell);
		}
	}

	public static void main(String[] args) {
		// "D:\\eclipse_workspace\\indigo\\TestExcel\\tiku.xls"
		ExcelModel eModel = ExcelHandler
				.getExcelData("D:\\eclipse_workspace\\indigo\\shoutchinese-robot\\res\\北京版《小学英语第一册》2.xls");

		ExcelHandler
				.writeExcel(eModel,
						"D:\\eclipse_workspace\\indigo\\shoutchinese-robot\\res\\北京版《小学英语第一册》2.xls");
		// System.out.println(eModel.getHeader());
		// Map m1 = eModel.getContent().get(1);
		// System.out.println(m1.get(eModel.getHeader().get(2)));
		// ExcelHandler
	}
}
